package db;

import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Set;
import java.util.Vector;

public class ebDBDataSet {

    private ebDB DB = ebDB.getInstance();
    private String tableName;
    private String selectSQL;
    private String insertSQL;
    private String updateSQL;
    private String deleteSQL;
    private String refreshSQL;
    private Vector<String> selectParamsVector;
    private Vector<String> insertParamsVector;
    private Vector<String> updateParamsVector;
    private Vector<String> deleteParamsVector;
    private Vector<String> refreshParamsVector;
    private PreparedStatement selectStatement;
    private PreparedStatement insertStatement;
    private PreparedStatement updateStatement;
    private PreparedStatement deleteStatement;
    private PreparedStatement refreshStatement;
    private boolean prepared;
    private boolean opened;
    private ResultSet rs;
    private ResultSetMetaData rsmd;
    private Vector<eDBDataSetMetaData> dbDataSetMetaData;
    /* MasterDataSet Veza */
    //private ebDBDataSet MasterDataSet;

    public ebDBDataSet() {
        prepared = false;
        opened = false;
    }

    public ResultSet getResultSet() {
        if (opened) {
            return rs;
        } else {
            return null;
        }
    }

    public ResultSetMetaData getResultSetMetaData() {
        if (opened) {
            return rsmd;
        } else {
            return null;
        }
    }

    public boolean setCustomSelectSQL(String customSelectSQL, String mainTableInSelect) {
        // Glavna tabela je tabela koja se pojavljuje iza FROM u upitu
        // Moze se i automatski pronaci parsovanjem customSelectSQL
        this.tableName = mainTableInSelect;
        boolean result = this.rebuildSQLQueries();
        selectSQL = customSelectSQL;
        return result;
    }

    public boolean setTableName(String tableName) {
        this.tableName = tableName;
        return this.rebuildSQLQueries();
    }

    private boolean rebuildSQLQueries() {
        try {
            String dbName = DB.getDatabaseName();
            DatabaseMetaData dmd = DB.getDatabaseMetaData();
            ResultSet pkrs = dmd.getPrimaryKeys(dbName, "dbo", this.tableName);
            ResultSet tcrs = dmd.getColumns(dbName, "dbo", this.tableName, null);
            String pkWhere = "";
            String insertFields = "";
            String insertParams = "";
            String updateFields = "";

            insertParamsVector = new Vector<String>();
            updateParamsVector = new Vector<String>();
            deleteParamsVector = new Vector<String>();
            refreshParamsVector = new Vector<String>();

            while (pkrs.next()) {
                if (pkWhere.length() > 0) {
                    pkWhere += " and ";
                }
                //pkWhere += "(" + pkrs.getString(4) + " = :" + pkrs.getString(4) + ")";
                pkWhere += "(" + pkrs.getString(4) + " = ?" + ")"; // + pkrs.getString(4) + ")";
                updateParamsVector.add(pkrs.getString(4));
                deleteParamsVector.add(pkrs.getString(4));
                refreshParamsVector.add(pkrs.getString(4));
            }
            pkrs.close();
            pkrs = null;

            pkWhere = "(" + pkWhere + ")";

            boolean columnIsInPK = false;
            int insertIndex = 0;
            dbDataSetMetaData = new Vector<eDBDataSetMetaData>();

            while (tcrs.next()) {
                if (insertFields.length() > 0) {
                    insertFields += ", ";
                }
                insertFields += tcrs.getString(4);
                insertParamsVector.add(tcrs.getString(4));
                if (insertParams.length() > 0) {
                    insertParams += ", ";
                }
                //insertParams += ":" + tcrs.getString(4);
                insertParams += "?"; // + tcrs.getString(4);

                pkrs = dmd.getPrimaryKeys(dbName, "dbo", this.tableName); // Budz, moze jednostavnije
                columnIsInPK = false;

                while (pkrs.next()) {
                    if (tcrs.getString(4).compareTo(pkrs.getString(4)) == 0) {
                        columnIsInPK = true;
                        break;
                    }
                }
                pkrs.close();
                pkrs = null;

                if (!columnIsInPK) {
                    if (updateFields.length() > 0) {
                        updateFields += ", ";
                    }
                    //updateFields += tcrs.getString(4) + " = :" + tcrs.getString(4);
                    updateFields += tcrs.getString(4) + " = ?"; // + tcrs.getString(4);
                    updateParamsVector.insertElementAt(tcrs.getString(4), insertIndex++);
                }

                dbDataSetMetaData.add(new eDBDataSetMetaData(tcrs.getString(4), tcrs.getInt(5)));

            }

            tcrs.close();
            tcrs = null;

            insertFields = "(" + insertFields + ")";
            insertParams = "(" + insertParams + ")";

            selectSQL = "select * from " + this.tableName;
            insertSQL = "insert into " + this.tableName + " " + insertFields + " values " + insertParams;
            updateSQL = "update " + this.tableName + " set " + updateFields + " where " + pkWhere;
            deleteSQL = "delete from " + this.tableName + " where " + pkWhere;
            refreshSQL = selectSQL + " where " + pkWhere;

            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean Prepare() {
        if (!prepared) {
            try {
                selectStatement = DB.getConnection().prepareStatement(selectSQL);
                insertStatement = DB.getConnection().prepareStatement(insertSQL);
                updateStatement = DB.getConnection().prepareStatement(updateSQL);
                deleteStatement = DB.getConnection().prepareStatement(deleteSQL);
                refreshStatement = DB.getConnection().prepareStatement(refreshSQL);
                prepared = true;
            } catch (Exception e) {
                e.printStackTrace();
                prepared = false;
            }
        }
        return prepared;
    }

    public boolean Open() {
        if (!opened) {
            try {
                rs = selectStatement.executeQuery();
                rsmd = rs.getMetaData();
                opened = true;
            } catch (Exception e) {
                e.printStackTrace();
                opened = false;
            }
        }
        return opened;
    }

    public boolean Unprepare() {
        prepared = false;
        return !prepared;
    }

    public boolean Close() {
        if (opened) {
            try {
                selectStatement.close();
                insertStatement.close();
                updateStatement.close();
                deleteStatement.close();
                refreshStatement.close();
                opened = false;
            } catch (Exception e) {
                e.printStackTrace();
                opened = true;
            }
        }
        return opened;
    }

    private int getColumnType(String columnName) {
        int result = java.sql.Types.VARCHAR;
        for (Iterator<eDBDataSetMetaData> it = dbDataSetMetaData.iterator(); it.hasNext();) {
            eDBDataSetMetaData dsmd = it.next();
            if (dsmd.columnName.compareTo(columnName) == 0) {
                result = dsmd.columnType;
            }
        }
        return result;
    }

    private boolean setSQLParams(PreparedStatement st, Vector<String> parametersLocations, LinkedHashMap<String, Object> paramValues) {

        try {
            // Postavljanje svih parametara u upitu na null
            for (int i = 0; i < parametersLocations.size(); i++) {
                st.setNull(i + 1, getColumnType(parametersLocations.elementAt(i)));
            }

            Set<String> tmpValues = paramValues.keySet();

            // prolazak kroz sve SQL parametre ili hes mapu i postavljanje
            // SQL parametara na vrednosti u hash mapi
            for (Iterator<String> it = tmpValues.iterator(); it.hasNext();) {
                String key = it.next();
                int paramIndex = parametersLocations.indexOf(key) + 1;
                if (paramIndex == 0)
                    continue;
                
                int paramType = getColumnType(parametersLocations.elementAt(paramIndex - 1));

                if (paramValues.get(key) == null) {
                    st.setNull(paramIndex, paramType);
                } else {
                    switch (paramType) {
                        case java.sql.Types.BIT:
                            st.setBoolean(paramIndex, Boolean.valueOf(paramValues.get(key).toString()));
                            break;
                        case java.sql.Types.TINYINT:
                        case java.sql.Types.SMALLINT:
                        case java.sql.Types.INTEGER:
                        case java.sql.Types.BIGINT:
                            st.setInt(paramIndex, Integer.parseInt(paramValues.get(key).toString()));
                            break;
                        case java.sql.Types.CHAR:
                        case java.sql.Types.VARCHAR:
                            st.setString(paramIndex, paramValues.get(key).toString());
                            break;
                        case java.sql.Types.DATE:
                            st.setDate(paramIndex, Date.valueOf(paramValues.get(key).toString()));
                            break;
                        case java.sql.Types.TIME:
                            st.setTime(paramIndex, Time.valueOf(paramValues.get(key).toString()));
                            break;
                        case java.sql.Types.TIMESTAMP:
                            st.setTimestamp(paramIndex, Timestamp.valueOf(paramValues.get(key).toString()));
                            break;
                        default:
                            st.setString(paramIndex, paramValues.get(key).toString());
                            break;
                    }
                }
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
  
    @SuppressWarnings("unchecked")
	public boolean enableFilter(HashMap<String, Object> filterValues) { /* samo oni po kojima se filtrira */

        Set<String> tmpValues = filterValues.keySet();
        String whereConditions = "";
        selectParamsVector = new Vector<String>();
        
        LinkedHashMap filterValuesLHM = new LinkedHashMap();
        for (Iterator<String> it = tmpValues.iterator(); it.hasNext(); )
        {
            String key = it.next();
            if (filterValues.get(key) == null)
                continue;
            filterValuesLHM.put(key, filterValues.get(key));
        }

        tmpValues = filterValuesLHM.keySet();
        
        for (Iterator<String> it = tmpValues.iterator(); it.hasNext();) {
            String key = it.next();
            selectParamsVector.add(key);
            
            if (whereConditions.length() > 0) {
                whereConditions += " and ";
            }

            if ((getColumnType(key) == Types.VARCHAR) || (getColumnType(key) == Types.CHAR)) {
                whereConditions += "(" + key + " LIKE ?)";
                filterValuesLHM.put(key, '%' + filterValuesLHM.get(key).toString() + '%');
            } else {
                whereConditions += "(" + key + " = ?)";
            }
        }
        
        whereConditions = "(" + whereConditions + ")";
        
        try {
            selectStatement = DB.getConnection().prepareStatement(selectSQL + " where " + whereConditions);
            if (setSQLParams(selectStatement, selectParamsVector, filterValuesLHM)) {
                rs = selectStatement.executeQuery();
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean disableFilter() {
        try {
            if (selectParamsVector != null)
            	selectParamsVector.clear();
            selectStatement = DB.getConnection().prepareStatement(selectSQL);
            return refreshDataSet();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    
    public boolean refreshDataSet() {
        try {
            rs = selectStatement.executeQuery();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean insert(LinkedHashMap<String, Object> insertValues) { /* sve */

        try {
            if (setSQLParams(insertStatement, insertParamsVector, insertValues)) {
                insertStatement.execute();
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(LinkedHashMap<String, Object> updateValues) { /* sve */

        try {
            if (setSQLParams(updateStatement, updateParamsVector, updateValues)) {
                updateStatement.execute();
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean delete(LinkedHashMap<String, Object> deleteValues) { /* samo primarni kljuc */

        try {
            if (setSQLParams(deleteStatement, deleteParamsVector, deleteValues)) {
                deleteStatement.execute();
                return true;
            } else {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean emptyTable() {
        try {
            PreparedStatement ps = ebDB.getInstance().getConnection().prepareStatement("delete from " + this.tableName);
            ps.execute();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    public int printableColumnSize(int i, int a) {
		try {
			return Math.max(rsmd.getColumnDisplaySize(i), a);
		} catch (Exception e) {
			e.printStackTrace();
			return 10;
		}
	}

    @Override
    public String toString() {
        String tmp = "";
        try {
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += "SIUDR SQL for table " + this.tableName + "\n";
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += selectSQL + "\n";
            tmp += "Select params: " + selectParamsVector + "\n";
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += insertSQL + "\n";
            tmp += "Insert params: " + insertParamsVector + "\n";
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += updateSQL + "\n";
            tmp += "Update params: " + updateParamsVector + "\n";
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += deleteSQL + "\n";
            tmp += "Delete params: " + deleteParamsVector + "\n";
            tmp += "--------------------------------------------------------------------------------\n";
            tmp += refreshSQL + "\n";
            tmp += "Refresh params: " + refreshParamsVector + "\n";
            tmp += "--------------------------------------------------------------------------------\n";

            int cols = rsmd.getColumnCount();
            tmp += "--------------------------------------------------------------------------------\n";
            for (int i = 1; i <= cols; i++) {
                tmp += String.format("%" + Math.max(rsmd.getColumnDisplaySize(i), rsmd.getColumnName(i).length()) + "s\t", rsmd.getColumnName(i));
            }
            tmp += "\n";
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    tmp += String.format("%" + Math.max(rsmd.getColumnDisplaySize(i), rsmd.getColumnName(i).length()) + "s\t", rs.getString(i));
                }
                tmp += "\n";
            }
            tmp += "--------------------------------------------------------------------------------\n";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tmp;
    }
}
